package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.servlet.http.HttpSession;

import model.GroupInfo;
import model.PageBean;
import model.TableInfo;
import net.sf.json.JSONArray;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import common.Constants;

import util.JdbcUtil;
import util.JsonUtil;
import exception.SystemException;

public class GroupInfoDao {

	private static final Log log = LogFactory.getLog(GroupInfoDao.class);

	public JSONArray findAllGroupInfo(String keyword, PageBean pageBean) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		int start = pageBean.getStart();
		int rows = pageBean.getRows();
		String sql = rows == 0 ? querySql(keyword) : querySql(keyword, start, rows);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int getGroupInfoCount(String keyword)
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = countSql(keyword);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				// return rs.getInt("total");
				return rs.getInt("count");
			} else {
				return 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int deleteGroupInfo(String owner,
			String groupName) throws SystemException {
		String sql = "delete from optim.modudf_group where owner = '"
				+ owner + "' and group_name='" + groupName + "'";
		log.debug(sql);
		Connection conn = JdbcUtil.getAdminConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int addGroupInfo(GroupInfo groupInfo)
			throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "insert into optim.modudf_group(owner,group_name,group_source,source_schema) values(?,?,?,?)";
		log.debug(sql);
		List<TableInfo> groupSources = groupInfo.getGroupSource();
		try {
			PreparedStatement ps = null;
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			if (groupSources != null) {
				for (int i = 0; i < groupSources.size(); i++) {
					TableInfo tableInfo = groupSources.get(i);
					ps.setString(1, groupInfo.getOwner());
					ps.setString(2, groupInfo.getGroupName());
					ps.setString(3, tableInfo.getTableName());
					ps.setString(4, tableInfo.getOwner());
					ps.addBatch();
				}
			} else {
				ps.setString(1, groupInfo.getOwner());
				ps.setString(2, groupInfo.getGroupName());
				ps.setString(3, "");
				ps.setString(4, "");
				ps.addBatch();
			}
			int[] result = ps.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
			return result.length;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}

	}

	public JSONArray getTablesByOwnerGroup(HttpSession session, String owner,
			String groupName, PageBean pageBean) {
		Connection conn = JdbcUtil.getConnection(session);
		int from = pageBean.getStart() + 1;
		int to = pageBean.getPage() * pageBean.getRows();
		String sql = to == 0 ? queryByOwnerGroupSql(owner, groupName)
				: queryByOwnerGroupSql(owner, groupName, from, to);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int getTablesCountByOwnerGroup(HttpSession session, String owner,
			String groupName) throws SystemException {
		Connection conn = JdbcUtil.getConnection(session);
		String sql = countSqlByOwnerGroup(owner, groupName);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("count");
			} else {
				return 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int[] grouTables(List<List<TableInfo>> groups) {
		Connection conn = JdbcUtil.getAdminConnection();
		Statement stmt = null;
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.addBatch("delete from optim.modudf_group");
			for (int i = 0; i < groups.size(); i++) {
				List<TableInfo> tables = groups.get(i);
				for (int j = 0; j < tables.size(); j++) {
					TableInfo tableInfo = tables.get(j);
					String sql = "insert into optim.modudf_group(owner,group_name,group_source,source_schema) values('"
							+ Constants.ADMIN_USER_NAME
							+ "','group"
							+ (i + 1)
							+ "','"
							+ tableInfo.getTableName()
							+ "','"
							+ tableInfo.getOwner() + "')";
					stmt.addBatch(sql);
					log.debug(sql);
				}
			}
			int[] result = stmt.executeBatch();
			JdbcUtil.commitConnection(conn);
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			JdbcUtil.rollbackConnection(conn);
			throw new SystemException(e);
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			try {
				conn.setAutoCommit(true);
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 拼接查询的sql语句
	private String querySql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select a.* from (select OWNER, GROUP_NAME from OPTIM.MODUDF_GROUP group by OWNER,GROUP_NAME) a where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	// 拼接查询码内建视图的sql语句
	private String querySql(String keyword, int start, int rows) {
		StringBuffer sb = new StringBuffer("(" + this.querySql(keyword) + ") ");
		sb.append("limit " + start + "," + rows);
		return sb.toString();
	}

	// 拼接查询总条数的sql语句
	private String countSql(String keyword) {
		StringBuffer sb = new StringBuffer(
				"select count(distinct group_name) as count from optim.modudf_group a where 1=1 ");
		sb.append(conditionSql(keyword));
		return sb.toString();
	}

	private String conditionSql(String keyword) {
		if (keyword != null && keyword.length() > 0) {
			StringBuffer sb = new StringBuffer();
			sb.append(" and a.group_name like '%").append(keyword).append("%'");
			return sb.toString();
		}
		return "";
	}

	private String countSqlByOwnerGroup(String owner, String groupName) {
		StringBuffer sb = new StringBuffer(
				"select count(*) count from  optim.modudf_group m where m.owner='"
						+ owner + "' and m.group_name='" + groupName + "'");
		return sb.toString();
	}

	private String queryByOwnerGroupSql(String owner, String groupName) {
		StringBuffer sb = new StringBuffer(
				"select group_source as tab_name,source_schema as owner,(select num_rows from dba_tables where owner = t.source_schema and table_name=t.group_source) as num_rows,(select sum(bytes)/1024/1024 from dba_extents where owner=t.source_schema and segment_name=t.group_source) as memory from optim.modudf_group t where t.source_schema is not null and t.group_source is not null and t.owner='"
						+ owner + "' and t.group_name='" + groupName + "'");
		return sb.toString();
	}

	private String queryByOwnerGroupSql(String owner, String groupName,
			int from, int to) {
		StringBuffer sb = new StringBuffer("select t.* from (");
		sb.append(this.queryByOwnerGroupSql(owner, groupName))
				.append(")r where r.rn between ").append(from).append(" and ")
				.append(to);
		return sb.toString();
	}

	public static void main(String[] args) {
		Connection conn = JdbcUtil.getConnection(
				"jdbc:oracle:thin:@127.0.0.01:1521:oracle", "optim", "optim");
		String sql = "create or replace function transAccount(c varchar2) return varchar2 as language java name 'dataPrivacy15.transAccount(java.lang.String) return java.lang.String'";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}
}
